/*
 * Copyright 2010-2012 Susanta Tewari. <freecode4susant@users.sourceforge.net>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

/*
* FileToRDBMover.java
*
* Created on December 17, 2007, 11:23 PM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
 */
package db.gui;

import db.genomemap.RDBSettings;
import db.gui.RDBConnection;

import fs.genomemap.FileSettings;

import genomemap.data.ChData;

import javautil.collections.ArrayUtil;
import javautil.collections.ListUtil;
import javautil.io.IOUtil;
import javautil.lang.StringUtil;
import javautil.swing.components.ProgressSupport;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashSet;
import java.util.StringTokenizer;
import java.util.Vector;

import javax.swing.*;

/**
 *
 * @author stewari1
 */
class FileToRDBMover extends ProgressSupport {

    /** Field description */
    private RDBConnection rdbConnection = null;

    /**
     * Constructs ...
     *
     * @param rdbConnection description
     */
    public FileToRDBMover(RDBConnection rdbConnection) {
        this.rdbConnection = rdbConnection;
    }

    public void createSchema() {

        if (rdbConnection == null) return;

        String DATABASE_NAME = rdbConnection.getConnectionModel().getSchema();
        Statement stmt       = rdbConnection.getStatement();

        try {


            // drop database if exits and create
            stmt.execute("DROP DATABASE IF EXISTS " + DATABASE_NAME + ";");
            stmt.execute("CREATE DATABASE " + DATABASE_NAME + ";");
            setStatusInfo("Database " + DATABASE_NAME + " is created \n");
            setOverAllProgress(5);
            create_TABLE_CHROMOSOME(0, 5);
            create_TABLE_CLONE(5, 10);
            create_TABLE_PROBE(15, 10);
            create_TABLE_RFLP(25, 20);
            create_TABLE_NCU(45, 10);
            create_TABLE_GENE(55, 10);
            create_TABLE_HYBRIDS(65, 35);


            // close connection
            rdbConnection.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        beep();
    }

    public void updateSeqInfo() {

        if (rdbConnection == null) return;

        String DATABASE_NAME = rdbConnection.getConnectionModel().getSchema();
        Statement stmt       = rdbConnection.getStatement();

        try {

            update_TABLE_GENE(0, 50);
            update_TABLE_CLONE(50, 50);


            // close connection
            rdbConnection.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (InterruptedException ex) {

            Runnable runnable = new Runnable() {

                public void run() {
                    setStatusInfo("The process has been Interrupted !");
                }
            };

            SwingUtilities.invokeLater(runnable);
            ex.printStackTrace();
        }

        beep();
    }

    /**
     * Method description
     *
     * @param overAllProgStart description
     * @param overAllProgLen description
     */
    private void create_TABLE_CHROMOSOME(int overAllProgStart, int overAllProgLen) {

        String TABLE_CHROMOSOME = RDBSettings.TABLE_CHROMOSOME;
        String DATABASE_NAME    = rdbConnection.getConnectionModel().getSchema();
        Statement stmt          = rdbConnection.getStatement();
        String query            = "";

        try {


            // MSG---BEFORE-creating  TABLE_CHROMOSOME
            String msg = "Creating TABLE " + DATABASE_NAME + "." + TABLE_CHROMOSOME;

            setStatusInfo(msg);
            setLog(msg + "\n");


            // creating  TABLE_CHROMOSOME
            query = "DROP TABLE IF EXISTS " + DATABASE_NAME + "." + TABLE_CHROMOSOME;

            stmt.execute(query);

            query = "CREATE TABLE  " + DATABASE_NAME + "." + TABLE_CHROMOSOME + "( "
                    + "id int(10) unsigned NOT NULL auto_increment, "
                    + "ch_id int(10) unsigned NOT NULL, " + "name varchar(45) NOT NULL, "
                    + "ch_length int(10) unsigned NOT NULL, "
                    + "clone_length int(10) unsigned NOT NULL, "
                    + "probe_count int(10) unsigned NOT NULL, "
                    + "clone_count int(10) unsigned NOT NULL, "
                    + "prob_false_pos double NOT NULL, " + "prob_false_neg double NOT NULL, "
                    + "hybridTable varchar(25) NOT NULL, " + "PRIMARY KEY  (id) "
                    + ") ENGINE=InnoDB DEFAULT CHARSET=utf8 ";

            stmt.execute(query);


            // MSG---POST-creating  TABLE_CHROMOSOME
            msg = "TABLE " + DATABASE_NAME + "." + TABLE_CHROMOSOME + " is created";

            setStatusInfo(msg);
            setLog(msg + "\n");


            // MSG---BEFORE-populating  TABLE_CHROMOSOME
            msg = "Populating TABLE " + DATABASE_NAME + "." + TABLE_CHROMOSOME;

            setStatusInfo(msg);
            setLog(msg + "\n");


            // populating TABLE_CHROMOSOME
            for (int i = 0; i < 7; i++) {

                ChData chData = FileSettings.load().getChData(i + 1);

                query =
                    " INSERT INTO " + DATABASE_NAME + "." + TABLE_CHROMOSOME
                    + "(ch_id,name,ch_length,clone_length,gene_count,probe_count,clone_count,prob_false_pos,prob_false_neg,hybridTable) "
                    + " VALUES(" + (i + 1) + ",\"Chromosome-" + StringUtil.getRoman(i + 1) + "\","
                    + chData.getChLength() + "," + chData.getCloneLength() + ","
                    + chData.getGeneCount() + "," + chData.getProbeCount() + ","
                    + chData.getCloneCount() + "," + chData.getProbFalsePos() + ","
                    + chData.getProbFalseNeg() + chData.getHybridTable() + "\");";

                stmt.execute(query);
            }


            // MSG---POST-populating  TABLE_CHROMOSOME
            msg = "TABLE " + DATABASE_NAME + "." + TABLE_CHROMOSOME + " has been populated ";

            setStatusInfo(msg);
            setLog(msg + "\n");


            // PROGRESS-UPDATE -TABLE_CHROMOSOME
            setProgress(100);
            setOverAllProgress(overAllProgLen);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Method description
     *
     * @param overAllProgStart description
     * @param overAllProgLen description
     *
     * @throws InterruptedException description
     */
    private void create_TABLE_CLONE(int overAllProgStart, int overAllProgLen) {

        String TABLE_CLONE   = RDBSettings.TABLE_CLONE;
        String DATABASE_NAME = rdbConnection.getConnectionModel().getSchema();
        Statement stmt       = rdbConnection.getStatement();
        String query         = "";

        try {


            // MSG---BEFORE-creating  TABLE_CLONE
            String msg = "Creating TABLE " + DATABASE_NAME + "." + TABLE_CLONE;

            setStatusInfo(msg);
            setLog(msg + "\n");


            // creating  TABLE_CHROMOSOME
            query = "DROP TABLE IF EXISTS " + DATABASE_NAME + "." + TABLE_CLONE;

            stmt.execute(query);

            query =
                "CREATE TABLE  " + DATABASE_NAME + "." + TABLE_CLONE + "( "
                + "id int(10) unsigned NOT NULL auto_increment, "
                + "ch_id int(10) unsigned NOT NULL, " + "clone_name varchar(10) NOT NULL, "
                + "start int(10) unsigned default 0, " + "end int(10) unsigned default 0, "
                + "clone_order int(10) unsigned NOT NULL," + "PRIMARY KEY  (id), "
                + "KEY clonename USING BTREE (clone_name)"
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = \" clone names are made unique.\" ;";

            stmt.execute(query);


            // MSG---POST-creating  TABLE_CHROMOSOME
            msg = "TABLE " + DATABASE_NAME + "." + TABLE_CLONE + " is created";

            setStatusInfo(msg);
            setLog(msg + "\n");


            // MSG---BEFORE-populating  TABLE_CLONE
            msg = "Populating TABLE " + DATABASE_NAME + "." + TABLE_CLONE;

            setStatusInfo(msg);
            setLog(msg + "\n");


            // populating TABLE_CLONE
            for (int ch_id_index = 0; ch_id_index < 7; ch_id_index++) {


                // PROGRESS-UPDATE -TABLE_CLONE FOR A CHROMOSOME
                setProgress(0);
                setOverAllProgress(overAllProgStart
                                   + (int) ((double) (ch_id_index + 1) / 7 * overAllProgLen));


                // MSG--populating  TABLE_CLONE FOR A CHROMOSOME
                msg = "Populating TABLE " + DATABASE_NAME + "." + TABLE_CLONE + " for Chromosome -"
                      + (ch_id_index + 1);

                setStatusInfo(msg);
                setLog(msg + "\n");


                // get clone names
                String clone_names_filePath = FileSettings.dataCollectionDir
                                              + FileSettings.FILE_CLONE_NAME_PTRN
                                              + (ch_id_index + 1) + ".txt";
                String[] clone_names = IOUtil.getTokens(
                                           IOUtil.readFileContentToString(
                                               new File(clone_names_filePath)), null);
                int clone_count = clone_names.length;

                for (int i = 0; i < clone_count; i++) {

                    if (Thread.interrupted()) {
                        throw new InterruptedException();
                    }

                    query = " INSERT INTO " + DATABASE_NAME + "." + TABLE_CLONE
                            + "(ch_id,clone_name,clone_order)" + " VALUES(" + (ch_id_index + 1)
                            + "," + "\"" + clone_names[i] + "\"," + (i + 1) + ");";

                    stmt.execute(query);


                    // report the number of clones added
                    setStatusInfo("Added " + (i + 1) + "/" + clone_count
                                  + " clones for Chromosome -" + (ch_id_index + 1) + " to "
                                  + DATABASE_NAME + "." + TABLE_CLONE);
                    setProgress((int) ((double) i / clone_count * 100));
                }
            }


            // MSG---POST-populating  TABLE_CLONE
            msg = "TABLE " + DATABASE_NAME + "." + TABLE_CLONE + " has been populated";

            setStatusInfo(msg);
            setLog(msg + "\n");
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Method description
     *
     * @param overAllProgStart description
     * @param overAllProgLen description
     *
     * @throws InterruptedException description
     */
    private void create_TABLE_PROBE(int overAllProgStart, int overAllProgLen) {

        String TABLE_PROBE   = RDBSettings.TABLE_PROBE;
        String DATABASE_NAME = rdbConnection.getConnectionModel().getSchema();
        Statement stmt       = rdbConnection.getStatement();
        String query         = "";

        try {


            // MSG---BEFORE-creating  TABLE_PROBE
            String msg = "Creating TABLE " + DATABASE_NAME + "." + TABLE_PROBE;

            setStatusInfo(msg);
            setLog(msg + "\n");


            // creating  TABLE_PROBE
            query = "DROP TABLE IF EXISTS " + DATABASE_NAME + "." + TABLE_PROBE;

            stmt.execute(query);

            query =
                "CREATE TABLE  " + DATABASE_NAME + "." + TABLE_PROBE + "( "
                + "id int(10) unsigned NOT NULL auto_increment, "
                + "ch_id int(10) unsigned NOT NULL, " + "probe_name varchar(10) NOT NULL, "
                + "start int(10) unsigned default 0, " + "end int(10) unsigned default 0, "
                + "probe_order int(10) unsigned NOT NULL," + "PRIMARY KEY  (id), "
                + "KEY probename USING BTREE (probe_name)"
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = \" probe names are made unique.\" ;";

            stmt.execute(query);


            // MSG---POST-creating  TABLE_PROBE
            msg = "TABLE " + DATABASE_NAME + "." + TABLE_PROBE + " is created";

            setStatusInfo(msg);
            setLog(msg + "\n");


            // populating TABLE_PROBE
            for (int ch_id_index = 0; ch_id_index < 7; ch_id_index++) {


                // PROGRESS-UPDATE -TABLE_PROBE FOR A CHROMOSOME
                setProgress(0);
                setOverAllProgress(overAllProgStart
                                   + (int) ((double) (ch_id_index + 1) / 7 * overAllProgLen));


                // MSG--populating  TABLE_PROBE FOR A CHROMOSOME
                msg = "Populating TABLE " + DATABASE_NAME + "." + TABLE_PROBE + " for Chromosome -"
                      + (ch_id_index + 1);

                setStatusInfo(msg);
                setLog(msg + "\n");


                // get probe names
                String probe_names_filePath = FileSettings.dataCollectionDir
                                              + FileSettings.FILE_PROBE_NAMES_PTRN
                                              + (ch_id_index + 1) + ".txt";
                String[] probe_names = IOUtil.getTokens(
                                           IOUtil.readFileContentToString(
                                               new File(probe_names_filePath)), null);
                int probe_count = probe_names.length;

                for (int i = 0; i < probe_count; i++) {

                    if (Thread.interrupted()) {
                        throw new InterruptedException();
                    }

                    query = " INSERT INTO " + DATABASE_NAME + "." + TABLE_PROBE
                            + "(ch_id,probe_name,probe_order)" + " VALUES(" + (ch_id_index + 1)
                            + "," + "\"" + probe_names[i] + "\"," + (i + 1) + ");";

                    stmt.execute(query);


                    // report the number of probes added
                    setStatusInfo("Added " + (i + 1) + "/" + probe_count
                                  + " clones to Chromosome -" + (ch_id_index + 1) + " to "
                                  + DATABASE_NAME + "." + TABLE_PROBE);
                    setProgress((int) ((double) i / probe_count * 100));
                }
            }


            // MSG---POST-populating  TABLE_PROBE
            msg = "TABLE " + DATABASE_NAME + "." + TABLE_PROBE + " has been populated";

            setStatusInfo(msg);
            setLog(msg + "\n");
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Method description
     *
     * @param overAllProgStart description
     * @param overAllProgLen description
     *
     * @throws InterruptedException description
     */
    private void create_TABLE_RFLP(int overAllProgStart, int overAllProgLen) {

        String TABLE_RFLP    = RDBSettings.TABLE_RFLP;
        String DATABASE_NAME = rdbConnection.getConnectionModel().getSchema();
        Statement stmt       = rdbConnection.getStatement();
        String query         = "";

        try {


            // MSG---BEFORE-creating  TABLE_RFLP
            String msg = "Creating TABLE " + DATABASE_NAME + "." + TABLE_RFLP;

            setStatusInfo(msg);
            setLog(msg + "\n");


            // creating TABLE_RFLP
            query = "DROP TABLE IF EXISTS " + DATABASE_NAME + "." + TABLE_RFLP;

            stmt.execute(query);

            query = "CREATE TABLE  " + DATABASE_NAME + "." + TABLE_RFLP + "( "
                    + "id int(10) unsigned NOT NULL auto_increment, "
                    + "ch_id int(10) unsigned NOT NULL, " + "rowId int(10) unsigned NOT NULL, "
                    + "progenyId int(10) unsigned NOT NULL, " + "value int(2) unsigned NOT NULL,"
                    + "PRIMARY KEY  (id)"
                    + ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = \" contains rflp data.\" ;";

            stmt.execute(query);


            // MSG---POST-creating  TABLE_PROBE
            msg = "TABLE " + DATABASE_NAME + "." + TABLE_RFLP + " is created";

            setStatusInfo(msg);
            setLog(msg + "\n");


            // populate TABLE_RFLP
            for (int ch_id_index = 0; ch_id_index < 7; ch_id_index++) {


                // PROGRESS-UPDATE -TABLE_PROBE FOR A CHROMOSOME
                setProgress(0);
                setOverAllProgress(overAllProgStart
                                   + (int) ((double) (ch_id_index + 1) / 7 * overAllProgLen));


                // MSG--populating  TABLE_PROBE FOR A CHROMOSOME
                msg = "Populating TABLE " + DATABASE_NAME + "." + TABLE_RFLP + " for Chromosome -"
                      + (ch_id_index + 1);

                setStatusInfo(msg);
                setLog(msg + "\n");


                // get rflp file path
                String rflp_filePath = FileSettings.dataCollectionDir + FileSettings.FILE_RFLP_PTRN
                                       + (ch_id_index + 1) + ".txt";
                int[][] rflpData = ArrayUtil.get2DIntMArray(new File(rflp_filePath), false, ",");
                int total_obs    = rflpData.length * rflpData[0].length;
                int counter      = 0;

                for (int i = 0; i < rflpData.length; i++) {

                    for (int j = 0; j < rflpData[0].length; j++) {

                        if (Thread.interrupted()) {
                            throw new InterruptedException();
                        }

                        counter++;

                        query = " INSERT INTO " + DATABASE_NAME + "." + TABLE_RFLP
                                + "(ch_id,rowId,progenyId,value)" + " VALUES(" + (ch_id_index + 1)
                                + "," + (i + 1) + "," + (j + 1) + "," + rflpData[i][j] + ");";

                        stmt.execute(query);


                        // report the number of rflp records added
                        setStatusInfo("Added " + counter + "/" + total_obs
                                      + " rflp records to Chromosome -" + (ch_id_index + 1)
                                      + " to " + DATABASE_NAME + "." + TABLE_RFLP);
                        setProgress((int) ((double) (counter) / total_obs * 100));
                    }
                }
            }


            // MSG---POST-populating  TABLE_PROBE
            msg = "TABLE " + DATABASE_NAME + "." + TABLE_RFLP + " has been populated";

            setStatusInfo(msg);
            setLog(msg + "\n");
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Method description
     *
     * @param overAllProgStart description
     * @param overAllProgLen description
     *
     * @throws InterruptedException description
     */
    private void create_TABLE_HYBRIDS(int overAllProgStart, int overAllProgLen) {

        String TABLE_HYBRID  = RDBSettings.TABLE_HYBRID_PTRN;
        String DATABASE_NAME = rdbConnection.getConnectionModel().getSchema();
        Statement stmt       = rdbConnection.getStatement();
        String query         = "";

        try {


            // TABLE  hybrid_1,...
            for (int ch_id_index = 0; ch_id_index < 7; ch_id_index++) {


                // MSG---BEFORE-creating  TABLE_HYBRID + (ch_id_index + 1)
                String msg = "Creating TABLE " + DATABASE_NAME + "." + TABLE_HYBRID
                             + (ch_id_index + 1);

                setStatusInfo(msg);
                setLog(msg + "\n");


                // creating TABLE_HYBRID + (ch_id_index + 1)
                query = "DROP TABLE IF EXISTS " + DATABASE_NAME + TABLE_HYBRID + (ch_id_index + 1);

                stmt.execute(query);


                // get probe names
                String probe_names_filePath = FileSettings.dataCollectionDir
                                              + FileSettings.FILE_PROBE_NAMES_PTRN
                                              + (ch_id_index + 1) + ".txt";
                String[] probe_names = IOUtil.getTokens(
                                           IOUtil.readFileContentToString(
                                               new File(probe_names_filePath)), "\n");


                // get clone names
                String clone_names_filePath = FileSettings.dataCollectionDir
                                              + FileSettings.FILE_CLONE_NAME_PTRN
                                              + (ch_id_index + 1) + ".txt";
                String[] clone_names = IOUtil.getTokens(
                                           IOUtil.readFileContentToString(
                                               new File(clone_names_filePath)), "\n");
                int probe_count = probe_names.length;
                int clone_count = clone_names.length;

                query = "CREATE TABLE  " + TABLE_HYBRID + (ch_id_index + 1) + "( "
                        + "id int(10) unsigned NOT NULL auto_increment, "
                        + "clone_order int(10) unsigned NOT NULL,";

                for (int i = 0; i < probe_count; i++) {

                    query += probe_names[i] + " int(2) unsigned NOT NULL,";
                }

                query +=
                    "PRIMARY KEY  (id), " + "KEY cloneorder USING BTREE (clone_order)"
                    + ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = \" Hybridization data.\" ;";

                stmt.execute(query);


                // MSG---POST-creating  TABLE_HYBRID + (ch_id_index + 1)
                msg = "TABLE " + DATABASE_NAME + "." + TABLE_HYBRID + (ch_id_index + 1)
                      + " is created";

                setStatusInfo(msg);
                setLog(msg + "\n");


                // MSG---BEFORE populating  TABLE_HYBRID + (ch_id_index + 1)
                msg = "Populating TABLE " + DATABASE_NAME + TABLE_HYBRID + (ch_id_index + 1);

                setStatusInfo(msg);
                setLog(msg + "\n");


                // set progress
                setProgress(0);
                setOverAllProgress(overAllProgStart
                                   + (int) ((double) (ch_id_index + 1) / 7 * overAllProgLen));


                // get hybrid file path
                String hybrid_filePath = FileSettings.dataCollectionDir
                                         + FileSettings.FILE_HYBRID_PTRN + (ch_id_index + 1)
                                         + ".txt";


                // populate  TABLE_HYBRID + (ch_id_index + 1)
                int[][] hybridData = ArrayUtil.get2DIntMArray(new File(hybrid_filePath), false,
                                         ",");
                String insertStr = " INSERT INTO " + TABLE_HYBRID + (ch_id_index + 1)
                                   + "(clone_order,";

                for (int i = 0; i < probe_count - 1; i++) {

                    insertStr += probe_names[i] + ",";
                }

                insertStr += probe_names[probe_count - 1] + ") VALUES(";

                for (int i = 0; i < clone_count; i++) {

                    if (Thread.interrupted()) {
                        throw new InterruptedException();
                    }

                    query = insertStr + (i + 1) + ",";

                    for (int j = 0; j < probe_count - 1; j++) {

                        query += hybridData[i][j] + ",";
                    }

                    query += hybridData[i][probe_count - 1] + ");";

                    stmt.execute(query);


                    // report the number of hybridization rows added
                    setStatusInfo("Added " + (i + 1) + "/" + clone_count
                                  + " hybridization rows Chromosome -" + (ch_id_index + 1) + " to "
                                  + DATABASE_NAME + TABLE_HYBRID + (ch_id_index + 1));
                    setProgress((int) ((double) i / clone_count * 100));
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Method description
     *
     * @param overAllProgStart description
     * @param overAllProgLen description
     *
     * @throws InterruptedException description
     */
    private void create_TABLE_GENE(int overAllProgStart, int overAllProgLen) {

        String TABLE_GENE    = RDBSettings.TABLE_GENE;
        String DATABASE_NAME = rdbConnection.getConnectionModel().getSchema();
        Statement stmt       = rdbConnection.getStatement();
        String query         = "";

        try {


            // MSG---BEFORE-creating  TABLE_GENE
            String msg = "Creating TABLE " + DATABASE_NAME + "." + TABLE_GENE;

            setStatusInfo(msg);
            setLog(msg + "\n");


            // creating TABLE_GENE
            query = "DROP TABLE IF EXISTS " + DATABASE_NAME + "." + TABLE_GENE;

            stmt.execute(query);

            query =
                "CREATE TABLE  " + DATABASE_NAME + "." + TABLE_GENE + "( "
                + "id int(10) unsigned NOT NULL auto_increment, "
                + "ch_id int(10) unsigned NOT NULL, " + "gene_name varchar(20) NOT NULL, "
                + "start int(10) unsigned default 0, " + "end int(10) unsigned default 0, "
                + "rowId int(10) unsigned NOT NULL, " + "gene_order int(10) unsigned NOT NULL,"
                + "PRIMARY KEY  (id), " + "KEY genename USING BTREE (gene_name)"
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = \" gene names are made unique.\" ;";

            stmt.execute(query);


            // MSG---POST-creating  TABLE_GENE
            msg = "TABLE " + DATABASE_NAME + "." + TABLE_GENE + " is created";

            setStatusInfo(msg);
            setLog(msg + "\n");


            // populate TABLE_GENE
            for (int ch_id_index = 0; ch_id_index < 7; ch_id_index++) {


                // PROGRESS-UPDATE -TABLE_PROBE FOR A CHROMOSOME
                setProgress(0);
                setOverAllProgress(overAllProgStart
                                   + (int) ((double) (ch_id_index + 1) / 7 * overAllProgLen));


                // MSG--populating  TABLE_PROBE FOR A CHROMOSOME
                msg = "Populating TABLE " + DATABASE_NAME + "." + TABLE_GENE + " for Chromosome -"
                      + (ch_id_index + 1);

                setStatusInfo(msg);
                setLog(msg + "\n");


                // get geneRowId file path
                String geneRowId_filePath = FileSettings.dataCollectionDir
                                            + FileSettings.FILE_GENE_ROW_ID_PTRN
                                            + (ch_id_index + 1) + ".txt";
                String[][] geneRowId_info =
                    ArrayUtil.ReadRaggedStr2DArray(new File(geneRowId_filePath), false, ",");
                int gene_count = geneRowId_info.length;

                for (int i = 0; i < gene_count; i++) {

                    if (Thread.interrupted()) {
                        throw new InterruptedException();
                    }

                    query = " INSERT INTO " + DATABASE_NAME + "." + TABLE_GENE
                            + "(ch_id,rowId,gene_name,gene_order)" + " VALUES(" + (ch_id_index + 1)
                            + "," + geneRowId_info[i][1] + "," + "\"" + geneRowId_info[i][0]
                            + "\"," + (i + 1) + ");";

                    stmt.execute(query);


                    // report the number of geneRowIds added
                    setStatusInfo("Added " + (i + 1) + "/" + gene_count
                                  + " geneRowIds to Chromosome -" + (ch_id_index + 1) + " to "
                                  + DATABASE_NAME + "." + TABLE_GENE);
                    setProgress((int) ((double) i / gene_count * 100));
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Method description
     *
     * @param overAllProgStart description
     * @param overAllProgLen description
     *
     * @throws InterruptedException description
     */
    private void create_TABLE_NCU(int overAllProgStart, int overAllProgLen) {

        String TABLE_NCU     = RDBSettings.TABLE_NCU;
        String DATABASE_NAME = rdbConnection.getConnectionModel().getSchema();
        Statement stmt       = rdbConnection.getStatement();
        String query         = "";

        try {


            // MSG---BEFORE-creating  TABLE_GENE
            String msg = "Creating TABLE " + DATABASE_NAME + "." + TABLE_NCU;

            setStatusInfo(msg);
            setLog(msg + "\n");


            // creating TABLE_NCU
            query = "DROP TABLE IF EXISTS " + DATABASE_NAME + "." + TABLE_NCU;

            stmt.execute(query);

            query =
                "CREATE TABLE  " + DATABASE_NAME + "." + TABLE_NCU + "( "
                + "id int(10) unsigned NOT NULL auto_increment, "
                + "ncu_name varchar(10) NOT NULL, " + "start int(10) unsigned NOT NULL, "
                + "end int(10) unsigned NOT NULL, " + "PRIMARY KEY  (id), "
                + "KEY ncu_name USING BTREE (ncu_name)"
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = \" ncu names are made unique.\" ;";

            stmt.execute(query);


            // MSG---POST-creating  TABLE_GENE
            msg = "TABLE " + DATABASE_NAME + "." + TABLE_NCU + " is created";

            setStatusInfo(msg);
            setLog(msg + "\n");


            // MSG---BEFORE-populating  TABLE_GENE
            msg = "Populating TABLE " + DATABASE_NAME + "." + TABLE_NCU;

            setStatusInfo(msg);
            setLog(msg + "\n");
            setProgress(0);


            // populate TABLE_NCU
            // read the ncu data
            String ncu_start_end_bank_filePath = FileSettings.dataCollectionDir
                                                 + FileSettings.FILE_NCU_START_END_BANK;
            int ncu_count      = IOUtil.countObs(new File(ncu_start_end_bank_filePath), false);
            String[] ncu_names = new String[ncu_count];
            int[] start        = new int[ncu_count];
            int[] end          = new int[ncu_count];

            try {

                try (BufferedReader in =
                        new BufferedReader(new FileReader(ncu_start_end_bank_filePath))) {

                    String line        = in.readLine();
                    StringTokenizer tk = null;
                    int count          = 0;

                    while (line != null) {

                        tk               = new StringTokenizer(line);
                        ncu_names[count] = tk.nextToken();
                        start[count]     = Integer.parseInt(tk.nextToken());
                        end[count]       = Integer.parseInt(tk.nextToken());

                        count++;

                        line = in.readLine();
                    }
                }

            } catch (IOException | NumberFormatException e) {
                e.printStackTrace();
            }

            for (int i = 0; i < ncu_count; i++) {

                if (Thread.interrupted()) {
                    throw new InterruptedException();
                }

                setOverAllProgress(overAllProgStart
                                   + (int) ((double) i / ncu_count * overAllProgLen));

                query = " INSERT INTO " + DATABASE_NAME + "." + TABLE_NCU + "(ncu_name,start,end)"
                        + " VALUES(" + "\"" + ncu_names[i] + "\"," + start[i] + "," + end[i] + ");";

                stmt.execute(query);


                // report the number of ncus added
                setStatusInfo("Added " + (i + 1) + "/" + ncu_count + " NCUs to " + DATABASE_NAME
                              + "." + TABLE_NCU);
                setProgress((int) ((double) i / ncu_count * 100));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Method description
     *
     * @param overAllProgStart description
     * @param overAllProgLen description
     *
     * @throws InterruptedException description
     */
    private void update_TABLE_GENE(int overAllProgStart, int overAllProgLen)
            throws InterruptedException {

        String TABLE_NCU     = RDBSettings.TABLE_NCU;
        String TABLE_GENE    = RDBSettings.TABLE_GENE;
        String DATABASE_NAME = rdbConnection.getConnectionModel().getSchema();
        Statement stmt       = rdbConnection.getStatement();
        String query         = "";

        try {

            for (int ch_id_index = 0; ch_id_index < 7; ch_id_index++) {

                setProgress(0);
                setOverAllProgress(0 + (int) ((double) (ch_id_index + 1) / 7 * 50));


                // read the gene_ncu data
                String gene_ncu_filePath = FileSettings.dataCollectionDir
                                           + FileSettings.FILE_GENE_NCU_PTRN + (ch_id_index + 1)
                                           + ".txt";
                LinkedHashSet<String> genesSet = new LinkedHashSet<>();
                LinkedHashSet<String> ncuSet   = new LinkedHashSet<>();

                try {

                    BufferedReader in  = new BufferedReader(new FileReader(gene_ncu_filePath));
                    String line        = in.readLine();
                    StringTokenizer tk = null;

                    while (line != null) {

                        tk = new StringTokenizer(line);

                        String gene = tk.nextToken();
                        String ncu  = tk.nextToken();

                        if (genesSet.contains(gene) || ncuSet.contains(ncu)) {

                            setLog("Duplicate pair: " + gene + "-" + ncu + " has been dropped."
                                   + "File location: " + gene_ncu_filePath + "\n");

                        } else {

                            genesSet.add(gene);
                            ncuSet.add(ncu);
                        }

                        line = in.readLine();
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                }


                // @todo file integrity check: should be placed in CheckFileIntegrity
                String[] genes = genesSet.toArray(new String[0]);
                String[] ncus  = ncuSet.toArray(new String[0]);

                if (genes.length != ncus.length) {

                    System.out.println(" lengths do`nt match for ch_id_index = " + ch_id_index
                                       + " from update_TABLE_GENE() ");
                    System.out.println(genesSet);
                    System.out.println(ncuSet);
                    System.exit(0);
                }


                // file integrity check:
                for (int i = 0; i < genes.length; i++) {

                    if (Thread.interrupted()) {
                        throw new InterruptedException();
                    }

                    query = " UPDATE genes,ncu SET genes.start = ncu.start,genes.end = ncu.end "
                            + " WHERE genes.gene_name = \"" + genes[i] + "\" AND ncu.ncu_name = \""
                            + ncus[i] + "\";";

                    stmt.execute(query);


                    // report the number of gene-ncus added
                    setStatusInfo("Updated sequence information of " + (i + 1) + "/" + genes.length
                                  + " genes to Chromosome -" + (ch_id_index + 1) + " to "
                                  + DATABASE_NAME + "." + TABLE_GENE);
                    setProgress((int) ((double) (i + 1) / genes.length * 100));
                    setOverAllProgress(overAllProgStart
                                       + (int) ((double) (i + 1) / genes.length * overAllProgLen));
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Method description
     *
     * @param overAllProgStart description
     * @param overAllProgLen description
     *
     * @throws InterruptedException description
     */
    private void update_TABLE_CLONE(int overAllProgStart, int overAllProgLen)
            throws InterruptedException {

        String TABLE_CLONE   = RDBSettings.TABLE_CLONE;
        String TABLE_GENE    = RDBSettings.TABLE_GENE;
        String DATABASE_NAME = rdbConnection.getConnectionModel().getSchema();
        Statement stmt       = rdbConnection.getStatement();
        String query         = "";

        try {


            // read the ncu data
            String clone_start_end_bank_filePath = FileSettings.dataCollectionDir
                                                   + FileSettings.FILE_CLONE_START_END_BANK;
            LinkedHashSet<String> cloneSet = new LinkedHashSet<>();
            Vector startVec                = new Vector();
            Vector endVec                  = new Vector();

            try {

                BufferedReader in =
                    new BufferedReader(new FileReader(clone_start_end_bank_filePath));
                String line        = in.readLine();
                StringTokenizer tk = null;

                while (line != null) {

                    tk = new StringTokenizer(line);

                    String clone = tk.nextToken();

                    if (cloneSet.contains(clone)) {

                        setLog("Duplicate clone: " + clone + " has been dropped \n"
                               + "File location: " + clone_start_end_bank_filePath + "\n");

                    } else {
                        cloneSet.add(clone);
                    }

                    startVec.add(Integer.parseInt(tk.nextToken()));
                    endVec.add(Integer.parseInt(tk.nextToken()));

                    line = in.readLine();
                }

            } catch (IOException | NumberFormatException e) {
                e.printStackTrace();
            }


            // @todo file integrity check: should be placed in CheckFileIntegrity
            String[] clones = cloneSet.toArray(new String[0]);
            int[] starts    = ListUtil.vector1DToInt1D(startVec);
            int[] ends      = ListUtil.vector1DToInt1D(endVec);

            if ((clones.length != starts.length) && (starts.length != ends.length)) {

                System.out.println(" lengths do`nt match  from update_TABLE_CLONE() ");
                System.out.println("clone length : " + clones.length);
                System.out.println("starts length : " + starts.length);
                System.out.println("ends length : " + ends.length);
                System.exit(0);
            }


            // file integrity check
            for (int i = 0; i < clones.length; i++) {

                if (Thread.interrupted()) {
                    throw new InterruptedException();
                }

                query = " UPDATE probe SET probe.start = " + starts[i] + ", probe.end = " + ends[i]
                        + " WHERE probe.probe_name = \"" + clones[i] + "\" ;";

                stmt.execute(query);

                query = " UPDATE clone SET clone.start = " + starts[i] + ", clone.end = " + ends[i]
                        + " WHERE clone.clone_name = \"" + clones[i] + "\" ;";

                stmt.execute(query);


                // report the number of clone/probe seqInfo added
                setStatusInfo("Updated sequence information of " + (i + 1) + "/" + clones.length
                              + " clones/probes to " + DATABASE_NAME + "." + TABLE_CLONE);
                setProgress((int) ((double) (i + 1) / clones.length * 100));
                setOverAllProgress(overAllProgStart
                                   + (int) ((double) (i + 1) / clones.length * overAllProgLen));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
